package com.zjzywl.smoke.repository.impl;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.zjzywl.smoke.config.AppConfig;
import com.zjzywl.smoke.repository.AlarmCustomRepository;
import com.zjzywl.smoke.util.NotDispose;

@Repository
public class AlarmRepositoryImpl {
	@PersistenceContext
	private EntityManager em;

	public List getAlarmByalarmTypeId(Integer userId, String alarmTypeCode) {
		String sql = " from Alarm a where a.alarmType.alarmCode=" + alarmTypeCode + " and a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + ")";
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getDeviceAlarmCountByTime(String b, String e, Integer byCount) {
		String sql = "select a.device,count(*) as alarmcount from Alarm a "
				+ " where a.alarmType.alarmTypeId=1 and a.beginTime >= '" + b + "' and a.beginTime <='" + e + "'"
				+ " group by a.device " + " having count(*)>" + byCount.toString() + " order by count(*) desc";
		Query query = em.createQuery(sql);
		query.setMaxResults(20);
		return query.getResultList();
	}

	public List getDeviceAlarmCountByTimeEx(String b, String e, Integer byCount, String alarmCode, String deviceTypeId,
			String userId) {
		String sql = "select a.device,count(*) as alarmcount from Alarm a " + " where a.beginTime >= '" + b
				+ "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		sql = sql + " group by a.device " + " having count(*)>" + byCount.toString() + " order by count(*) desc";
		Query query = em.createQuery(sql);
		query.setMaxResults(20);
		return query.getResultList();
	}

	public List getSpeciesAlarmCountByTimeEx(String b, String e, String alarmCode, String fireCount, String userId) {
		String sql = "select a.device,a.device.fireCount from Alarm a  " + " where a.beginTime >= '" + b
				+ "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		if (Integer.parseInt(fireCount) == 5) {
			sql = sql + " and a.device.fireCount>='" + fireCount + "'" + " and a.device.fireCount<='" + 10 + "'";
		}
		if (Integer.parseInt(fireCount) == 10) {
			sql = sql + " and a.device.fireCount>='" + fireCount + "'";
		}
		if (Integer.parseInt(fireCount) == 2) {
			sql = sql + " and a.device.fireCount<='" + fireCount + "'";
		}
		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		sql = sql + " group by a.device ";
		Query query = em.createQuery(sql);
		query.setMaxResults(20);
		return query.getResultList();
	}

	public List getOrgAlarmCountByTime(String b, String e, Integer orgid) {
		String sql = "select DATE_FORMAT(a.beginTime,'%Y-%m-%d') as rq,count(*) as alarmcount from Alarm a "
				+ " where a.alarmType.alarmTypeId=1 and a.beginTime >= '" + b + "' and a.beginTime <='" + e + "'"
				+ " group by DATE_FORMAT(a.beginTime,'%Y-%m-%d') " + " order by DATE_FORMAT(a.beginTime,'%Y-%m-%d')";
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getOrgAlarmCountByTimeEx(String b, String e, Integer orgid, String alarmCode, String deviceTypeId,
			String userId) {
		String sql = "select DATE_FORMAT(a.beginTime,'%Y-%m-%d') as rq,count(*) as alarmcount from Alarm a "
				+ " where a.beginTime >= '" + b + "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}

		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}
		sql = sql + " group by DATE_FORMAT(a.beginTime,'%Y-%m-%d') " + " order by DATE_FORMAT(a.beginTime,'%Y-%m-%d')";
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getOrgAlarmCountByAlarmReasons(String b, String e, Integer orgid, String alarmCode, String deviceTypeId,
			String userId) {
		String sql = "select a.alarmReasons as alarmReasons,count(*) as alarmcount from Alarm a "
				+ " where a.beginTime >= '" + b + "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		sql += "and a.alarmReasons!=null";
		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}
		sql = sql + " group by a.alarmReasons " + " order by DATE_FORMAT(a.beginTime,'%Y-%m-%d')";
		Query query = em.createQuery(sql);

		return query.getResultList();
	}

	public List getOrgAlarmCountByAlarmReasons(String b, String e, String alarmCode, String deviceTypeId, String userId,
			String alarmReasons) {
		String sql = "select a.alarmReasons as alarmReasons,count(*) as alarmcount from Alarm a "
				+ " where a.beginTime >= '" + b + "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		sql += " and a.alarmReasons!=null";
		sql += " and a.alarmReasons='" + alarmReasons + "'";
		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getOrgAlarmCountByDevice(String b, String e, Integer orgid, String alarmCode, String deviceTypeId,
			String userId) {
		String sql = "select a.device.fixedPlace,count(*) as alarmcount from Alarm a " + " where a.beginTime >= '" + b
				+ "' and a.beginTime <='" + e + "' ";
		sql += " and a.device.fixedPlace!=null ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}

		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "' ";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}
		sql = sql + " group by a.device.fixedPlace ";
		System.out.println(sql);
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getOrgAlarmCountByDevice(String b, String e, String alarmCode, String deviceTypeId, String userId,
			String fixedPlace) {
		String sql = "select a.device.fixedPlace,count(*) as alarmcount from Alarm a " + " where a.beginTime >= '" + b
				+ "' and a.beginTime <='" + e + "' ";
		sql += " and a.device.fixedPlace!=null ";
		sql += " and a.device.fixedPlace='" + fixedPlace + "'";

		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}

		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "' ";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getAlarmCountByDevice(String b, String e, Integer orgid, String alarmCode, String deviceTypeId,
			String userId, String fixedPlace) {
		String sql = "select a from Alarm a " + " where a.beginTime >= '" + b + "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}
		if (validStr(fixedPlace)) {
			if (fixedPlace.equals("all")) {

			} else {
				sql = sql + " and a.device.fixedPlace=" + "'" + fixedPlace + "'";
			}
		}
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getByDisposeStatusAndAlarmType(String userId, Integer status, String alarmCode) {
		String sql = " from Alarm a where a.alarmType.alarmCode='" + alarmCode+"'" + " and a.disposeStatus!=" + 0
				+ " and a.device in (" + " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
				+ " and c.userId = " + userId + ")";

		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getAlarmByTimeAndAlarmTypeAndAddressAndUser(String bTime, String eTime, String alarmCode,
			String deviceTypeId, String address, String userId) {
		String sql = " select a.device.devCode ,a.device.address,a.device.fixedPlace,a.alarmType.alarmName,(select u.org.orgName from SysUser u where u.userId="
				+ userId + "),a.beginTime from Alarm a where a.beginTime >= '" + bTime + "' and a.beginTime<='" + eTime
				+ "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		if (validStr(address)) {
			sql = sql + " and a.address like '%" + address + "%' ";
		}
		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		System.out.println(sql);

		Query query = em.createQuery(sql);
		return query.getResultList();

	}

	public List getAlarmByTimeAndAlarmTypeAndAddressAndUserSimple(String bTime, String eTime, String alarmCode,
			String deviceTypeId, String address, String userId) {
		String sql = "select a.device.devCode,a.device.deviceName,a.device.longitude,a.device.latitude,a.device.address,a.alarmType.alarmCode,a.beginTime"
				+ " from Alarm a where a.beginTime >= '" + bTime + "' and a.beginTime<='" + eTime + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		if (validStr(address)) {
			sql = sql + " and a.address like '%" + address + "%' ";
		}
		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		sql = sql + " order by a.alarmId";

		Query query = em.createQuery(sql);
		return query.getResultList();

	}

	private Boolean validStr(String value) {
		return (value != null) && (value.length() != 0) && (!value.equals("null"));
	}

	public List getByDisposeStatusAndAlarmTypeAndUser(String userId, Integer status, String alarmCode) {
		String sql = " select a.device.devCode ,a.device.address,a.device.fixedPlace,a.alarmType.alarmName,(select u.org.orgName from SysUser u where u.userId="
				+ userId
				+ "),a.beginTime ,a.alarmId ,a.device.longitude,a.device.latitude from Alarm a where a.alarmType.alarmCode='"
				+ alarmCode+"'" + " and a.disposeStatus=" + status + " and a.device in ("
				+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
				+ userId + ")";
		System.out.println(sql);
		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getByTimeAndDisposeStatusAndAlarmTypeAndUser(String currentWeekTime, String currentTime, String userId,
			Integer status, String alarmCode) {
		String sql = " from Alarm a where a.alarmType.alarmCode='" + alarmCode+"'" + " and a.disposeStatus=" + status
				+ " and a.device in (" + " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
				+ " and c.userId = " + userId + ")" + " and a.beginTime >= '" + currentWeekTime
				+ "' and a.beginTime <='" + currentTime + "' ";

		Query query = em.createQuery(sql);
		System.out.println(sql);
		return query.getResultList();
	}

	public List getAlarmCountByTime(String b, String e, String alarmCode, String userId) {
		String sql = "select count(*) as alarmcount from Alarm a " + " where a.beginTime >= '" + b
				+ "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		Query query = em.createQuery(sql);
		query.setMaxResults(20);
		return query.getResultList();
	}

	public List getAlarmCount(String b, String e, String alarmCode, String userId) {
		String sql = "select a  from Alarm a " + " where a.beginTime >= '" + b + "' and a.beginTime <='" + e + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		Query query = em.createQuery(sql);

		return query.getResultList();
	}

	public List getFixedPlaceByuser(String userId) {
		String sql = "select a.device.fixedPlace as fixedPlace from Alarm a ";

		if (validStr(userId)) {
			sql = sql + "where a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}
		sql += " and a.device.fixedPlace is not null";
		sql += " GROUP BY a.device.fixedPlace";
		Query query = em.createQuery(sql);
		query.setMaxResults(20);
		return query.getResultList();
	}

	public List findByDisposeStatusAndAlarmType(int i, String alarmFire) {
		String sql = "select u.device.devCode as devCode,u.device.address as address,u.device.deviceName as deviceName,u.alarmType.alarmName as alarmName, u.beginTime as beginTime from Alarm u where u.disposeStatus= "
				+ i + " and u.alarmType.alarmCode= " + alarmFire + " ";

		Query query = em.createQuery(sql);
		return query.getResultList();
	}

	public List getDSCountByUserAndDeviceTypeAndStatus(String userId, String status, String deviceTypeId,
			String startTime, String endTime, String disposePeople, String devCode) {
		String sql = "select a.device.devCode , a.device.address ,a.device.fixedPlace,a.alarmType.alarmName,a.beginTime ,a.disposePeople  ,a.disposeTime ,a.disposeContent ,a.device.fireCount "
				+ "from Alarm a where ";
		if (status.equals("all")) {
			sql += "a.alarmType.alarmTypeId in(2,3)";
		} else {
			sql += "a.alarmType.alarmTypeId=" + status;
		}
		sql += " and a.device in (" + " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
				+ " and c.userId = " + userId + " and b.device.deviceType.deviceTypeId=" + deviceTypeId + ")"
				+ " and a.beginTime >= '" + startTime + "' and a.beginTime<='" + endTime + "'";
		if (validStr(disposePeople)) {
			sql += " and a.disposePeople='" + disposePeople + "'";
		}
		if (validStr(devCode)) {
			sql += " and a.device.devCode='" + devCode+"'";
		}
		sql += " group by a.device.devCode";
		System.out.println(sql);
		Query query = em.createQuery(sql);
		return query.getResultList();
	}
	// public List getTimeDifferenceByBeginTimeAndEndTime(String b,String e, Integer
	// orgid,String alarmCode,String deviceTypeId, String userId) {
	// String sql = "select a.timeDifference from Alarm a "
	// + " where a.beginTime >= '"+b + "' and a.beginTime <='" + e +"' " ;
	// if(validStr(alarmCode)) {
	// sql = sql + " and a.alarmType.alarmCode="+alarmCode;
	// }
	//
	// if(validStr(deviceTypeId)) {
	// sql = sql + " and a.device.deviceType.deviceTypeId='"+deviceTypeId+"'";
	// }
	//
	// if(validStr(userId)) {
	// sql = sql + " and a.device in (" +
	// " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id
	// " +
	// " and c.userId = "+userId+")";
	// }
	// sql = sql + "a.timeDifference>0 ";
	// Query query = em.createQuery(sql);
	// return query.getResultList();
	// }

	public List getAlarmByTimeAndAlarmTypeAndAddressAndUserAndAlarmReasons(String bTime, String eTime, String alarmCode,
			String deviceTypeId, String address, String userId, String alarmReasons) {
		String sql = " select a.device.devCode ,a.device.address,a.device.fixedPlace,a.alarmType.alarmName,(select u.org.orgName from SysUser u where u.userId="
				+ userId
				+ "),a.beginTime ,a.disposePeople,a.disposeTime,a.alarmReasons from Alarm a where a.beginTime >= '"
				+ bTime + "' and a.beginTime<='" + eTime + "' ";
		if (validStr(alarmCode)) {
			sql = sql + " and a.alarmType.alarmCode='" + alarmCode+"'";
		}
		if (validStr(address)) {
			sql = sql + " and a.address like '%" + address + "%' ";
		}
		if (validStr(deviceTypeId)) {
			sql = sql + " and a.device.deviceType.deviceTypeId='" + deviceTypeId + "'";
		}
		if (validStr(alarmReasons)) {
			sql = sql + " and a.alarmReasons='" + alarmReasons + "'";
		}

		if (validStr(userId)) {
			sql = sql + " and a.device in ("
					+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id "
					+ " and c.userId = " + userId + ")";
		}

		System.out.println(sql);

		Query query = em.createQuery(sql);
		return query.getResultList();

	}
	
	public List getByDisposeStatusAndAlarmTypeAndUserAndTime(String beginTime,String endTime,String userId, Integer status, String alarmCode) {
	String sql = " select a.device.devCode ,a.device.address,a.device.fixedPlace,a.alarmType.alarmName,(select u.org.orgName from SysUser u where u.userId="
			+ userId + "),a.beginTime ,a.alarmId ,a.device.longitude,a.device.latitude from Alarm a where a.alarmType.alarmCode='" + alarmCode+"'"
			+ " and a.disposeStatus=" + status + " and a.device in ("
			+ " SELECT b.device FROM OrgDev b , SysUser c where b.organization.id=c.org.id " + " and c.userId = "
			+ userId + ")";
	sql+=" and a.beginTime >= '" + beginTime + "' and a.beginTime<='" + endTime + "'";
	System.out.println(sql);
	Query query = em.createQuery(sql);
	return query.getResultList();
}
}
